package repository

import (
	"blog_go/internal/model"
	"blog_go/pkg/common/fileoper"
	"blog_go/pkg/common/media"
	"blog_go/pkg/helper/uuid"
	"database/sql"
	"errors"
	"fmt"
	"mime/multipart"
	"path"
	"strconv"
	"strings"
	"time"
)

type ArticleRepository interface {
	QueryAllArticle(categoryId int, tagId int, userId int, page int, pageSize int) (*model.ArticleResult, error)
	QueryArticleByArticleId(articleId int, userId int) (*model.ArticleView, error)
	QueryUserArticle(userId int, targetUserId int, page int, pageSize int) (*model.ArticleResult, error)
	QueryArticle(userId int, userType int) ([]*model.ArticleView, error)
	QueryArticles(keyWords string, page int, pageSize int, userId int) (*model.ArticleResult, error)
	QueryCollectedArticle(userId int, collectionId int, page int, pageSize int) (*model.ArticleResult, error)
	AddArticle(article model.ArticleView) error
	AddMdArticle(article model.ArticleView) error
	UpdateArticle(article model.ArticleView, userId int) error
	UpdateMdArticle(article model.ArticleView, userId int) error
	UpdateUploadFile(files *multipart.Form) (string, error)
	DelArticle(articleIds []int, userId int, userType int) error
	LikeArticle(articleId int, userId int) error
	UnLikeArticle(articleId int, userId int) error
	DisLikeArticle(articleId int, userId int) error
	UnDisLikeArticle(articleId int, userId int) error
	CollectArticle(articleId int, userId int, collectionId int) error
	UnCollectArticle(articleId int, userId int) error
	ViewArticle(articleId int) error
	ReviewArticle(articleIds []int, status int) error
	ReviewAllArticle() error
	MoveArticle(articleId int, collectionId int) error
}

func NewArticleRepository(repository *BaseRepository) ArticleRepository {
	return &articleRepository{
		BaseRepository: repository,
	}
}

type articleRepository struct {
	*BaseRepository
}

// 浏览文章
func (r *articleRepository) ViewArticle(articleId int) error {
	_, err := r.db.Exec(`
		UPDATE 
			article 
		SET 
			views = views + 1 
		WHERE 
			article_id = ?`, articleId)
	if err != nil {
		return err
	}

	return nil
}

// markdown编辑器添加文章
func (r *articleRepository) AddMdArticle(article model.ArticleView) error {
	tx, err := r.db.Begin()
	if err != nil {
		return err
	}

	defer func() {
		if err != nil {
			tx.Rollback()
		}
	}()

	if article.Status == 2 {
		return errors.New("无审核权限")
	}

	//md转成html
	article.Content, err = fileoper.ConvertMarkdownToSafeHTML(article.MarkdownContent)
	if err != nil {
		return err
	}

	//mdcontent修改图片路径
	article.MarkdownContent = fileoper.UpdateMdContent(article.MarkdownContent)

	//移动资源至永久目录
	article.Content, err = fileoper.UpdateTempResources(article.Content, tx)
	if err != nil {
		return err
	}

	// 文章初始化
	article.CommentCount = 0
	article.Like = 0
	article.DisLike = 0
	article.View = 0
	article.CollectCount = 0
	article.UpdateTime = time.Now().Format("2006-01-02 15:04:05")

	ret, err := tx.Exec(`
		INSERT INTO article
		(
			title, 
			content,  
			markdown_content, 
			status, 
			author_id, 
			update_time, 
			likes, 
			views, 
			dislikes, 
			comment_count, 
			collect_count
		) 
		VALUES(?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?)`,
		article.Title, article.Content, article.MarkdownContent, article.Status, article.AuthorId, article.UpdateTime, article.Like, article.View, article.DisLike, article.CommentCount, article.CollectCount)
	if err != nil {
		return err
	}

	articleId, err := ret.LastInsertId()
	if err != nil {
		return err
	}

	stmt, err := tx.Prepare(`
		INSERT INTO article_tag
		(
			article_id, 
			tag_id
		) 
		values(?, ?)
	`)
	if err != nil {
		return err
	}

	for _, tagId := range article.TagIds {
		_, err = stmt.Exec(articleId, tagId)
		if err != nil {
			return err
		}
	}

	err = tx.Commit()
	return err
}

// 富文本编辑器添加文章
func (r *articleRepository) AddArticle(article model.ArticleView) error {
	tx, err := r.db.Begin()
	if err != nil {
		return err
	}

	defer func() {
		if err != nil {
			tx.Rollback()
		}
	}()

	if article.Status == 2 {
		return errors.New("无审核权限")
	}

	article.Content, err = fileoper.UpdateTempResources(article.Content, tx)
	if err != nil {
		return err
	}

	//文章初始化
	article.CommentCount = 0
	article.Like = 0
	article.DisLike = 0
	article.View = 0
	article.CollectCount = 0
	article.UpdateTime = time.Now().Format("2006-01-02 15:04:05")

	ret, err := tx.Exec(`
		INSERT INTO article
		(
			title, 
			content, 
			status, 
			author_id, 
			update_time, 
			likes, 
			views, 
			dislikes, 
			comment_count, 
			collect_count
		) 
		VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		article.Title, article.Content, article.Status, article.AuthorId, article.UpdateTime, article.Like, article.View, article.DisLike, article.CommentCount, article.CollectCount)
	if err != nil {
		return err
	}

	articleId, err := ret.LastInsertId()
	if err != nil {
		return err
	}

	if len(article.TagIds) == 0 {
		return nil
	}

	stmt, err := tx.Prepare(`
		INSERT INTO article_tag
		(
			article_id, 
			tag_id
		) 
		values(?, ?)
	`)
	if err != nil {
		return err
	}

	for _, tagId := range article.TagIds {
		_, err = stmt.Exec(articleId, tagId)
		if err != nil {
			return err
		}
	}

	err = tx.Commit()
	return err
}

// 富文本编辑器上传文件
func (r *articleRepository) UpdateUploadFile(files *multipart.Form) (string, error) {
	tx, err := r.db.Begin()
	if err != nil {
		return "", err
	}

	defer func() {
		if err != nil {
			tx.Rollback()
		}
	}()

	fileinfo := new(model.FileInfo)

	file := files.File["files"][0]

	fileinfo.FileType = path.Ext(file.Filename)
	fileinfo.FileName = uuid.GenUUID()

	isImg := media.In(fileinfo.FileType, media.ImgExtArr())
	isVideo := media.In(fileinfo.FileType, media.VideoExtArr())

	if isImg {
		//是图片
		fileinfo.FilePath = fileoper.ArticleImgPath + fileinfo.FileName + fileinfo.FileType
		fileinfo.FileUseTo = "文章图片"
	} else if isVideo {
		fileinfo.FilePath = fileoper.ArticleVideoPath + fileinfo.FileName + fileinfo.FileType
		fileinfo.FileUseTo = "文章视频"
	}

	fileId := fileoper.AddFileInfo(tx, fileinfo, file)

	if fileId == 0 {
		return "", errors.New("文件上传错误")
	}

	err = tx.Commit()
	return fileinfo.FilePath, err
}

// 修改文章
func (r *articleRepository) UpdateArticle(article model.ArticleView, userId int) error {
	//修改文章表和文件表
	tx, err := r.db.Begin()
	if err != nil {
		return err
	}

	defer func() {
		if err != nil {
			tx.Rollback()
		}
	}()

	if article.Status == 2 {
		return errors.New("无审核权限")
	}

	var authorId int
	err = tx.QueryRow(`
		SELECT 
			author_id 
		FROM 
			article 
		WHERE 
			article_id = ?`, article.ArticleId).Scan(&authorId)
	if err != nil {
		return err
	}

	if authorId != userId {
		return errors.New("用户Id和作者Id不匹配")
	}

	if article.MarkdownContent != "" {
		article.Content, err = fileoper.ConvertMarkdownToSafeHTML(article.MarkdownContent)
		if err != nil {
			return err
		}
	}

	//修改文件
	var htmlContent string

	tx.QueryRow(`
		SELECT 
			content 
		FROM 
			article 
		WHERE 
			article_id = ?`, article.ArticleId).Scan(&htmlContent)

	article.Content, err = fileoper.UpdateTempResources(article.Content, tx)
	if err != nil {
		return err
	}

	flag, err := fileoper.UpdateHtmlResources(htmlContent, article.Content, tx)
	if !flag {
		return err
	}

	article.MarkdownContent = fileoper.UpdateMdContent(article.MarkdownContent)

	//修改文章
	updateTime := time.Now().Format("2006-01-02 15:04:05")

	_, err = tx.Exec(`
		UPDATE 
			article 
		SET 
			title = ?, 
			content = ?, 
			markdown_content = ?, 
			status = ?, 
			update_time = ? 
		WHERE 
			article_id = ?`,
		article.Title, article.Content, article.MarkdownContent, article.Status, updateTime, article.ArticleId)
	if err != nil {
		return err
	}

	//删除文章标签关系
	_, err = tx.Exec(`
		DELETE FROM article_tag 
		WHERE 
			article_id = ?`, article.ArticleId)
	if err != nil {
		return err
	}

	if len(article.TagIds) == 0 {
		return nil
	}

	stmt, err := tx.Prepare(`
		INSERT INTO article_tag
		(
			article_id, 
			tag_id
		) 
		values(?, ?)
	`)
	if err != nil {
		return err
	}

	for _, tagId := range article.TagIds {
		_, err = stmt.Exec(article.ArticleId, tagId)
		if err != nil {
			return err
		}
	}

	err = tx.Commit()
	return err
}

// 修改md文章
func (r *articleRepository) UpdateMdArticle(article model.ArticleView, userId int) error {
	//修改文章表和文件表
	tx, err := r.db.Begin()
	if err != nil {
		return err
	}

	defer func() {
		if err != nil {
			tx.Rollback()
		}
	}()

	if article.Status == 2 {
		return errors.New("无审核权限")
	}

	var authorId int
	err = tx.QueryRow(`
		SELECT 
			author_id 
		FROM 
			article 
		WHERE 
			article_id = ?`, article.ArticleId).Scan(&authorId)
	if err != nil {
		return err
	}

	if authorId != userId {
		return errors.New("用户Id和作者Id不匹配")
	}

	var htmlContent string

	tx.QueryRow(`
		SELECT 
			content 
		FROM 
			article 
		WHERE 
			article_id = ?`, article.ArticleId).Scan(&htmlContent)

	article.Content, err = fileoper.ConvertMarkdownToSafeHTML(article.MarkdownContent)
	if err != nil {
		return errors.New("修改文章失败")
	}

	article.Content, err = fileoper.UpdateTempResources(article.Content, tx)
	if err != nil {
		return errors.New("修改文章失败")
	}

	flag, err := fileoper.UpdateHtmlResources(htmlContent, article.Content, tx)
	if !flag {
		return errors.New("修改文章失败")
	}

	article.MarkdownContent = fileoper.UpdateMdContent(article.MarkdownContent)

	//修改文章
	updateTime := time.Now().Format("2006-01-02 15:04:05")

	_, err = tx.Exec(`
		UPDATE 
			article 
		SET 
			title = ?, 
			content = ?, 
			markdown_content = ?, 
			status = ?, 
			update_time = ? 
		WHERE 
			article_id = ?`,
		article.Title, article.Content, article.MarkdownContent, article.Status, updateTime, article.ArticleId)
	if err != nil {
		return err
	}

	//删除文章标签关系
	_, err = tx.Exec(`
		DELETE FROM article_tag 
		WHERE 
			article_id = ?`, article.ArticleId)
	if err != nil {
		return err
	}

	stmt, err := tx.Prepare(`
		INSERT INTO article_tag
		(
			article_id, 
			tag_id
		) 
		values(?, ?)
	`)
	if err != nil {
		return err
	}

	for _, tagId := range article.TagIds {
		_, err = stmt.Exec(article.ArticleId, tagId)
		if err != nil {
			return err
		}
	}

	err = tx.Commit()
	return err
}

// 删除文章
func (r *articleRepository) DelArticle(articleIds []int, userId int, userType int) error {
	//删除文章要同时删除收藏夹里面的文章,标签里的文章关系， 文章的评论，富文本编辑器里的文件
	tx, err := r.db.Begin()
	if err != nil {
		return err
	}

	defer func() {
		if err != nil {
			tx.Rollback()
		}
	}()

	if userType == 1 {
		var authorId int
		for _, articleId := range articleIds {
			err = tx.QueryRow(`
				SELECT 
					author_id 
				FROM 
					article 
				WHERE 
					article_id = ?`, articleId).Scan(&authorId)
			if err != nil {
				return err
			}

			if authorId != userId {
				return errors.New("用户无权限删除文章")
			}
		}
	}

	//删除文章标签关系
	stmtDelTag, err := tx.Prepare("DELETE FROM article_tag WHERE article_id = ?")
	if err != nil {
		return err
	}
	defer stmtDelTag.Close()

	//删除收藏夹文章
	stmtDelCollection, err := tx.Prepare("DELETE FROM article_collection WHERE article_id = ?")
	if err != nil {
		return err
	}
	defer stmtDelCollection.Close()

	//删除文章的评论
	stmtDelComment, err := tx.Prepare("DELETE FROM article_comment WHERE article_id = ?")
	if err != nil {
		return err
	}
	defer stmtDelComment.Close()

	//删除文章
	stmtDelArticle, err := tx.Prepare("DELETE FROM article WHERE article_id = ?")
	if err != nil {
		return err
	}
	defer stmtDelArticle.Close()

	for _, articleId := range articleIds {
		_, err = stmtDelTag.Exec(articleId)
		if err != nil {
			return err
		}

		_, err = stmtDelCollection.Exec(articleId)
		if err != nil {
			return err
		}

		_, err = stmtDelComment.Exec(articleId)
		if err != nil {
			return err
		}

		//删除文章的文件
		var htmtcontent string
		tx.QueryRow("SELECT content FROM article WHERE article_id = ?", articleId).Scan(&htmtcontent)

		flag := fileoper.DelHtmlResources(htmtcontent, tx)
		if !flag {
			tx.Rollback()
			return errors.New("删除文章文件失败")
		}

		_, err = stmtDelArticle.Exec(articleId)
		if err != nil {
			return err
		}
	}

	err = tx.Commit()
	return err
}

// 根据标题、内容，模糊查询
func (r *articleRepository) QueryArticles(keyWords string, page int, pageSize int, userId int) (*model.ArticleResult, error) {
	articleResult := new(model.ArticleResult)

	query := `
		SELECT DISTINCT 
			a.article_id, 
			a.title, 
			a.content, 
			COALESCE(a.markdown_content, '') 'markdownContent', 
			a.status, 
			a.views, 
			a.likes, 
			a.dislikes, 
			a.comment_count, 
			a.collect_count, 
			a.update_time, 
			a.author_id,
		CASE WHEN EXISTS (SELECT 1 FROM article_like WHERE article_id = a.article_id AND user_id = ?) THEN 'true' ELSE 'false' END as is_liked, 
		CASE WHEN EXISTS (SELECT 1 FROM article_dislike WHERE article_id = a.article_id AND user_id = ?) THEN 'true' ELSE 'false' END as is_disliked, 
		CASE WHEN EXISTS (SELECT 1 FROM article_collection WHERE article_id = a.article_id AND user_id = ?) THEN 'true' ELSE 'false' END as is_collected 
		FROM 
			article a 
		WHERE 
			a.status = 2 
		AND (
			title LIKE ? 
			OR REGEXP_REPLACE(content, '<[^>]+>', '') LIKE ?)
		LIMIT ? OFFSET ?`

	offset := (page - 1) * pageSize

	rows, err := r.db.Query(query, userId, userId, userId, "%"+keyWords+"%", "%"+keyWords+"%", pageSize, offset)
	if err != nil {
		return nil, err
	}

	defer rows.Close()

	var total int
	articles := make([]*model.ArticleView, 0)

	for rows.Next() {
		var article model.ArticleView

		err := rows.Scan(&article.ArticleId, &article.Title, &article.Content, &article.MarkdownContent, &article.Status, &article.View, &article.Like, &article.DisLike, &article.CommentCount, &article.CollectCount,
			&article.UpdateTime, &article.AuthorId, &article.IsLiked, &article.IsDisLiked, &article.IsCollected)
		if err != nil {
			return nil, err
		}

		articles = append(articles, &article)
		total++
	}

	articleResult.Records = articles

	articleResult.Total = total

	return articleResult, nil
}

// 查询文章
func (r *articleRepository) QueryArticle(userId int, userType int) ([]*model.ArticleView, error) {
	articles := make([]*model.ArticleView, 0)

	query := `
		SELECT 
			a.article_id, 
			a.title, 
			a.content, 
			COALESCE(a.markdown_content, '') 'markdownContent',
			a.status, 
			a.views, 
			a.likes, 
			a.dislikes, 
			a.comment_count, 
			a.collect_count, 
			a.update_time, 
			a.author_id, 
		COALESCE(u.display_name, '') 'displayName', 
		COALESCE(u.signature, '') 'signature', COALESCE(f.file_path, '') 'filePath', 
		COALESCE(GROUP_CONCAT(t.tag_name), '') 'tagNames', 
		COALESCE(GROUP_CONCAT(t.tag_id), '') 'tagIds' 
		FROM 
			article a 
		LEFT JOIN 
			user u 
		ON 
			a.author_id = u.user_id
		LEFT JOIN 
			fileinfo f 
		ON 
			u.img_file_id = f.file_id
		LEFT JOIN 
			article_tag at 
		ON 
			a.article_id = at.article_id 
		LEFT JOIN 
			tag t 
		ON 
			at.tag_id = t.tag_id`

	var queryParams []interface{}

	if userType != 0 {
		query += " WHERE a.author_id = ? GROUP BY a.article_id"
		queryParams = append(queryParams, userId)
	} else {
		query += " WHERE a.status != 0 GROUP BY a.article_id"
	}

	rows, err := r.db.Query(query, queryParams...)
	if err != nil {
		return nil, err
	}

	defer rows.Close()

	for rows.Next() {
		var article model.ArticleView
		var tags, tagIds string

		err = rows.Scan(
			&article.ArticleId, &article.Title, &article.Content, &article.MarkdownContent, &article.Status, &article.View,
			&article.Like, &article.DisLike, &article.CommentCount, &article.CollectCount, &article.UpdateTime, &article.AuthorId,
			&article.DisplayName, &article.Signature, &article.ImgFilePath, &tags, &tagIds,
		)
		if err != nil {
			return nil, err
		}

		article.Tags = strings.Split(tags, ",")
		ids := strings.Split(tagIds, ",")
		article.TagIds = make([]int, 0, len(ids))

		for _, idString := range ids {
			idString = strings.TrimSpace(idString)
			if idString == "" {
				continue
			}

			id, err := strconv.Atoi(idString)
			if err != nil {
				return nil, err
			}

			article.TagIds = append(article.TagIds, id)
		}

		articles = append(articles, &article)
	}

	return articles, nil
}

// 根据分类、标签查询文章
func (r *articleRepository) QueryAllArticle(categoryId int, tagId int, userId int, page int, pageSize int) (*model.ArticleResult, error) {
	articleResult := new(model.ArticleResult)
	articles := make([]*model.ArticleView, 0)
	tagIds := make([]interface{}, 0)

	// 查询标签ID
	if tagId == 0 {
		var rows *sql.Rows
		var err error

		if categoryId == 0 {
			rows, err = r.db.Query("SELECT DISTINCT tag_id FROM article_category_tag")
		} else {
			rows, err = r.db.Query("SELECT tag_id FROM article_category_tag WHERE category_id = ?", categoryId)
		}

		if err != nil {
			return nil, err
		}

		defer rows.Close()

		for rows.Next() {
			var tagId int

			if err := rows.Scan(&tagId); err != nil {
				return nil, err
			}

			tagIds = append(tagIds, tagId)
		}

		if len(tagIds) == 0 {
			return articleResult, nil // 如果没有标签，则返回空列表
		}
	} else {
		tagIds = append(tagIds, tagId)
	}

	// 查询文章ID
	inClause := strings.Repeat("?,", len(tagIds)-1) + "?"
	query := fmt.Sprintf(`
		SELECT DISTINCT 
			article_tag.article_id 
		FROM 
			article_tag 
		LEFT JOIN 
			article 
		ON 
			article.article_id = article_tag.article_id
		WHERE 
			tag_id IN (%s) 
		AND article.status = 2`, inClause)

	articleRows, err := r.db.Query(query, tagIds...)
	if err != nil {
		return nil, err
	}

	defer articleRows.Close()

	articleIds := make([]interface{}, 0)
	for articleRows.Next() {
		var articleId int

		if err := articleRows.Scan(&articleId); err != nil {
			return nil, err
		}

		articleIds = append(articleIds, articleId)
	}

	if len(articleIds) == 0 {
		return articleResult, nil // 如果没有文章，则返回空列表
	}

	articleResult.Total = len(articleIds)

	// 查询文章信息及相关数据
	inClause = strings.Repeat("?,", len(articleIds)-1) + "?"
	query = fmt.Sprintf(`
		SELECT 
			a.article_id, 
			a.title, 
			a.content, 
			COALESCE(a.markdown_content, '') 'markdownContent', 
			a.status, 
			a.views, 
			a.likes, 
			a.dislikes, 
			a.comment_count, 
			a.collect_count, 
			a.update_time,
		CASE WHEN EXISTS (SELECT 1 FROM article_like WHERE article_id = a.article_id AND user_id = ?) THEN 'true' ELSE 'false' END as is_liked,
		CASE WHEN EXISTS (SELECT 1 FROM article_dislike WHERE article_id = a.article_id AND user_id = ?) THEN 'true' ELSE 'false' END as is_disliked,
		CASE WHEN EXISTS (SELECT 1 FROM article_collection WHERE article_id = a.article_id AND user_id = ?) THEN 'true' ELSE 'false' END as is_collected
		FROM 
			article a  
		WHERE 
			a.article_id IN (%s)
		ORDER BY a.update_time DESC`, inClause)

	//计算偏移量
	offset := (page - 1) * pageSize
	query += " LIMIT ? OFFSET ?"

	articleIds = append(articleIds, pageSize, offset)
	args := append([]interface{}{userId, userId, userId}, articleIds...)
	articleInfoRows, err := r.db.Query(query, args...)
	if err != nil {
		return nil, err
	}

	defer articleInfoRows.Close()

	for articleInfoRows.Next() {
		var article model.ArticleView

		err := articleInfoRows.Scan(&article.ArticleId, &article.Title, &article.Content, &article.MarkdownContent, &article.Status, &article.View, &article.Like, &article.DisLike,
			&article.CommentCount, &article.CollectCount, &article.UpdateTime, &article.IsLiked, &article.IsDisLiked, &article.IsCollected)

		if err != nil {
			return nil, err
		}

		articles = append(articles, &article)
	}

	articleResult.Records = articles

	return articleResult, nil
}

// 查询文章详情
func (r *articleRepository) QueryArticleByArticleId(articleId int, userId int) (*model.ArticleView, error) {
	article := new(model.ArticleView)

	var count int
	err := r.db.QueryRow(`
		SELECT 
			COUNT(*) 
		FROM 
			article 
		WHERE 
			article_id = ?`, articleId).Scan(&count)
	if err != nil {
		return nil, err
	}

	if count == 0 {
		return nil, errors.New("文章id不存在")
	}

	query := `
		SELECT 
			a.article_id, 
			a.title, 
			a.content, 
			COALESCE(a.markdown_content, '') 'markdownContent', 
			a.status, 
			a.views, 
			a.likes, 
			a.dislikes, 
			a.comment_count, 
			a.collect_count, 
			a.update_time, 
			a.author_id, 
			COALESCE(u.display_name, '') 'displayName', 
			COALESCE(u.signature, '') 'signature', 
			COALESCE(f.file_path, '') 'filePath',
			COALESCE(GROUP_CONCAT(t.tag_name), '') 'tags', COALESCE(GROUP_CONCAT(t.tag_id), '') 'tagIds',
		CASE WHEN EXISTS (SELECT 1 FROM article_like WHERE article_id = a.article_id AND user_id = ?) THEN 'true' ELSE 'false' END as is_liked,
		CASE WHEN EXISTS (SELECT 1 FROM article_dislike WHERE article_id = a.article_id AND user_id = ?) THEN 'true' ELSE 'false' END as is_disliked,
		CASE WHEN EXISTS (SELECT 1 FROM article_collection WHERE article_id = a.article_id AND user_id = ?) THEN 'true' ELSE 'false' END as is_collected,
		CASE WHEN EXISTS (SELECT 1 FROM user_follow WHERE follower_id = a.author_id AND user_id = ?) THEN 'true' ELSE 'false' END as is_followed
		FROM 
			article a 
		LEFT JOIN 
			user u 
		ON 
			a.author_id = u.user_id
		LEFT JOIN 
			fileinfo f 
		ON 
			u.img_file_id = f.file_id
		LEFT JOIN 
			article_tag at 
		ON 
			a.article_id = at.article_id
		LEFT JOIN 
			tag t 
		ON 
			at.tag_id = t.tag_id
		WHERE 
			a.article_id = ? 
		GROUP BY 
			a.article_id`

	var tags, tagIds string
	err = r.db.QueryRow(query, userId, userId, userId, userId, articleId).Scan(&article.ArticleId, &article.Title, &article.Content, &article.MarkdownContent, &article.Status, &article.View,
		&article.Like, &article.DisLike, &article.CommentCount, &article.CollectCount, &article.UpdateTime, &article.AuthorId, &article.DisplayName, &article.Signature,
		&article.ImgFilePath, &tags, &tagIds, &article.IsLiked, &article.IsDisLiked, &article.IsCollected, &article.IsFollowed)
	if err != nil {
		return nil, err
	}

	article.Tags = strings.Split(tags, ",")
	ids := strings.Split(tagIds, ",")

	article.TagIds = make([]int, 0, len(ids))

	for _, idString := range ids {
		idString = strings.TrimSpace(idString)
		if idString == "" {
			continue
		}

		id, err := strconv.Atoi(idString)
		if err != nil {
			return nil, err
		}

		article.TagIds = append(article.TagIds, id)
	}

	return article, nil
}

// 点赞文章
func (r *articleRepository) LikeArticle(articleId int, userId int) error {
	//查询是否有反对文章并删除
	tx, err := r.db.Begin()
	if err != nil {
		return err
	}

	defer func() {
		if err != nil {
			tx.Rollback()
		}
	}()

	var count int
	err = tx.QueryRow(`
		SELECT COUNT(*) 
		FROM article_like 
		WHERE 
			article_id = ? 
		AND user_id = ?`, articleId, userId).Scan(&count)

	if count > 0 {
		return errors.New("该文章已点赞")
	}

	err = tx.QueryRow(`
		SELECT 
			COUNT(*) 
		FROM 
			article_dislike 
		WHERE 
			article_id = ? 
		AND user_id = ?`, articleId, userId).Scan(&count)
	if err != nil {
		return err
	}

	if count > 0 {
		_, err = tx.Exec(`
			DELETE FROM article_dislike 
			WHERE 
				user_id = ? 
			AND article_id = ?`, userId, articleId)
		if err != nil {
			return err
		}

		_, err = tx.Exec(`
			UPDATE 
				article 
			SET 
				dislikes = dislikes - 1 
			WHERE 
				article_id = ?`, articleId)
		if err != nil {
			return err
		}
	}

	_, err = tx.Exec(`
		INSERT INTO article_like
		(
			user_id, 
			article_id
		) 
		VALUES(?, ?)`, userId, articleId)
	if err != nil {
		return err
	}

	_, err = tx.Exec(`
		UPDATE 
			article 
		SET 
			likes = likes + 1 
		WHERE 
			article_id = ?`, articleId)
	if err != nil {
		return err
	}

	err = tx.Commit()
	return err
}

// 取消点赞文章
func (r *articleRepository) UnLikeArticle(articleId int, userId int) error {
	tx, err := r.db.Begin()
	if err != nil {
		return err
	}

	defer func() {
		if err != nil {
			tx.Rollback()
		}
	}()

	var count int
	err = tx.QueryRow(`
		SELECT COUNT(*) 
		FROM article_like 
		WHERE 
			article_id = ? 
		AND user_id = ?`, articleId, userId).Scan(&count)

	if count == 0 {
		return errors.New("该文章已取消点赞")
	}

	_, err = tx.Exec(`
		DELETE FROM article_like 
		WHERE 
			user_id = ? 
		AND article_id = ?`, userId, articleId)
	if err != nil {
		return err
	}

	_, err = tx.Exec(`
		UPDATE 
			article 
		SET 
			likes = likes - 1 
		WHERE 
			article_id = ?`, articleId)
	if err != nil {
		return err
	}

	err = tx.Commit()
	return err
}

// 反对文章
func (r *articleRepository) DisLikeArticle(articleId int, userId int) error {
	tx, err := r.db.Begin()
	if err != nil {
		return err
	}

	defer func() {
		if err != nil {
			tx.Rollback()
		}
	}()

	var count int
	err = tx.QueryRow(`
		SELECT COUNT(*) 
		FROM article_dislike 
		WHERE 
			article_id = ? 
		AND user_id = ?`, articleId, userId).Scan(&count)

	if count > 0 {
		return errors.New("该文章已反对")
	}

	err = tx.QueryRow(`
		SELECT 
			COUNT(*) 
		FROM 
			article_like 
		WHERE 
			article_id = ? 
		AND 
			user_id = ?`, articleId, userId).Scan(&count)
	if err != nil {
		return err
	}

	if count > 0 {
		_, err = tx.Exec(`
			DELETE FROM article_like 
			WHERE 
				user_id = ? 
			AND 
				article_id = ?`, userId, articleId)
		if err != nil {
			return err
		}

		_, err = tx.Exec(`
			UPDATE 
				article 
			SET 
				likes = likes - 1 
			WHERE 
				article_id = ?`, articleId)
		if err != nil {
			return err
		}
	}

	_, err = tx.Exec(`
		INSERT INTO article_dislike
		(
			user_id, 
			article_id
		) 
		VALUES(?, ?)`, userId, articleId)
	if err != nil {
		return err
	}

	_, err = tx.Exec(`
		UPDATE 
			article 
		SET 
			dislikes = dislikes + 1 
		WHERE 
			article_id = ?`, articleId)
	if err != nil {
		return err
	}

	err = tx.Commit()
	return err
}

// 取消反对文章
func (r *articleRepository) UnDisLikeArticle(articleId int, userId int) error {
	tx, err := r.db.Begin()
	if err != nil {
		return err
	}

	defer func() {
		if err != nil {
			tx.Rollback()
		}
	}()

	var count int
	err = tx.QueryRow(`
		SELECT COUNT(*) 
		FROM article_dislike 
		WHERE 
			article_id = ? 
		AND user_id = ?`, articleId, userId).Scan(&count)

	if count == 0 {
		return errors.New("该文章已取消反对")
	}

	_, err = tx.Exec(`
		DELETE FROM article_dislike 
		WHERE 
			user_id = ? 
		AND article_id = ?`, userId, articleId)
	if err != nil {
		return err
	}

	_, err = tx.Exec(`
		UPDATE 
			article 
		SET 
			dislikes = dislikes - 1 
		WHERE 
			article_id = ?`, articleId)
	if err != nil {
		return err
	}

	err = tx.Commit()
	return err
}

// 收藏文章
func (r *articleRepository) CollectArticle(articleId int, userId int, collectionId int) error {
	tx, err := r.db.Begin()
	if err != nil {
		return err
	}

	defer func() {
		if err != nil {
			tx.Rollback()
		}
	}()

	var count int
	err = tx.QueryRow(`
		SELECT COUNT(*) 
		FROM article_collection 
		WHERE 
			article_id = ?, 
			user_id = ?
		AND collection_id = ?`, articleId, userId, collectionId).Scan(&count)

	if count > 0 {
		return errors.New("该文章已收藏")
	}

	_, err = tx.Exec(`
		INSERT INTO article_collection
		(
			collection_id, 
			article_id, 
			user_id
		) 
		VALUES (?, ?, ?)`, collectionId, articleId, userId)
	if err != nil {
		return err
	}

	_, err = tx.Exec(`
		UPDATE 
			article 
		SET 
			collect_count = collect_count + 1 
		WHERE 
			article_id = ?`, articleId)
	if err != nil {
		return err
	}

	err = tx.Commit()
	return err
}

// 取消收藏文章
func (r *articleRepository) UnCollectArticle(articleId int, userId int) error {
	tx, err := r.db.Begin()
	if err != nil {
		return err
	}

	defer func() {
		if err != nil {
			tx.Rollback()
		}
	}()

	var count int
	err = tx.QueryRow(`
		SELECT 
			COUNT(*) 
		FROM 
			article_collection 
		WHERE 
			article_id = ?
		AND	user_id = ?`, articleId, userId).Scan(&count)

	if count == 0 {
		return errors.New("该文章已取消收藏")
	}

	_, err = tx.Exec(`
		DELETE FROM article_collection 
		WHERE 
			user_id = ? 
		AND article_id = ?`, userId, articleId)
	if err != nil {
		return err
	}

	_, err = tx.Exec(`
		UPDATE 
			article 
		SET 
			collect_count = collect_count - 1 
		WHERE 
			article_id = ?`, articleId)
	if err != nil {

		return err
	}

	err = tx.Commit()
	return err
}

// 审核文章
func (r *articleRepository) ReviewArticle(articleIds []int, status int) error {
	tx, err := r.db.Begin()
	if err != nil {
		return err
	}

	defer func() {
		if err != nil {
			tx.Rollback()
		}
	}()

	stmt, err := tx.Prepare(`
		UPDATE 
			article 
		SET 
			status = ? 
		WHERE 
			article_id = ?`)
	if err != nil {
		return err
	}

	defer stmt.Close()

	for _, articleId := range articleIds {
		_, err = stmt.Exec(status, articleId)
		if err != nil {
			return err
		}
	}

	err = tx.Commit()
	return err
}

// 移动收藏文章位置
func (r *articleRepository) MoveArticle(articleId int, collectionId int) error {
	_, err := r.db.Exec(`
		UPDATE 
			article_collection 
		SET 
			collection_id = ? 
		WHERE 
			article_id = ?`, collectionId, articleId)
	if err != nil {
		return err
	}

	return nil
}

// 查询收藏的文章
func (r *articleRepository) QueryCollectedArticle(userId int, collectionId int, page int, pageSize int) (*model.ArticleResult, error) {
	articleResult := new(model.ArticleResult)
	articles := make([]*model.ArticleView, 0)
	articleResult.Records = articles

	// 查询文章Id
	articleRows, err := r.db.Query(`
		SELECT 
			article_id 
		FROM 
			article_collection 
		WHERE 
			collection_id = ?`, collectionId)
	if err != nil {
		return nil, err
	}

	defer articleRows.Close()

	articleIds := make([]interface{}, 0)
	for articleRows.Next() {
		var articleId int

		if err := articleRows.Scan(&articleId); err != nil {
			return nil, err
		}

		articleIds = append(articleIds, articleId)
	}

	if len(articleIds) == 0 {
		return articleResult, nil // 如果没有文章，则返回空列表
	}

	// 查询文章信息及相关数据
	inClause := strings.Repeat("?,", len(articleIds)-1) + "?"
	query := fmt.Sprintf(`
		SELECT 
			a.article_id, 
			a.title, 
			a.content, 
			COALESCE(a.markdown_content, '') 'markdownContent', 
			a.status, 
			a.views, 
			a.likes, 
			a.dislikes, 
			a.comment_count, 
			a.collect_count, 
			a.update_time, 
			a.author_id,
		CASE WHEN EXISTS (SELECT 1 FROM article_like WHERE article_id = a.article_id AND user_id = ?) THEN 'true' ELSE 'false' END as is_liked, 
		CASE WHEN EXISTS (SELECT 1 FROM article_dislike WHERE article_id = a.article_id AND user_id = ?) THEN 'true' ELSE 'false' END as is_disliked, 
		CASE WHEN EXISTS (SELECT 1 FROM article_collection WHERE article_id = a.article_id AND user_id = ?) THEN 'true' ELSE 'false' END as is_collected
		FROM 
			article a 
		WHERE 
			a.article_id IN (%s) 
		AND a.status = 2 `, inClause)

	offset := (page - 1) * pageSize
	query += " LIMIT ? OFFSET ?"

	args := make([]interface{}, 0, len(articleIds)+4) // 预先分配容量
	args = append(args, userId, userId, userId)
	args = append(args, articleIds...)

	args = append(args, pageSize, offset)

	articleInfoRows, err := r.db.Query(query, args...)
	if err != nil {
		return nil, err
	}

	defer articleInfoRows.Close()

	var total int
	for articleInfoRows.Next() {
		var article model.ArticleView

		err := articleInfoRows.Scan(&article.ArticleId, &article.Title, &article.Content, &article.MarkdownContent, &article.Status, &article.View, &article.Like, &article.DisLike,
			&article.CommentCount, &article.CollectCount, &article.UpdateTime, &article.AuthorId,
			&article.IsLiked, &article.IsDisLiked, &article.IsCollected)

		if err != nil {
			return nil, err
		}

		articles = append(articles, &article)
		total++
	}

	articleResult.Records = articles

	articleResult.Total = total

	return articleResult, nil
}

// 查询他人文章
func (r *articleRepository) QueryUserArticle(userId int, targetUserId int, page int, pageSize int) (*model.ArticleResult, error) {
	articleResult := new(model.ArticleResult)
	articles := make([]*model.ArticleView, 0)

	query := `
		SELECT 
			a.article_id, 
			a.title, 
			a.content, 
			COALESCE(a.markdown_content, '') 'markdownContent', 
			a.status, 
			a.views, 
			a.likes, 
			a.dislikes, 
			a.comment_count, 
			a.collect_count, 
			a.update_time, 
			a.author_id,
		CASE WHEN EXISTS (SELECT 1 FROM article_like WHERE article_id = a.article_id AND user_id = ?) THEN 'true' ELSE 'false' END as is_liked,
		CASE WHEN EXISTS (SELECT 1 FROM article_dislike WHERE article_id = a.article_id AND user_id = ?) THEN 'true' ELSE 'false' END as is_disliked,
		CASE WHEN EXISTS (SELECT 1 FROM article_collection WHERE article_id = a.article_id AND user_id = ?) THEN 'true' ELSE 'false' END as is_collected
		FROM 
			article a 
		WHERE 
			a.author_id = ? 
		AND a.status = 2`

	offset := (page - 1) * pageSize
	query += " LIMIT ? OFFSET ?"

	rows, err := r.db.Query(query, userId, userId, userId, targetUserId, pageSize, offset)

	if err != nil {
		return nil, err
	}

	defer rows.Close()

	var total int

	for rows.Next() {
		var article model.ArticleView

		err = rows.Scan(
			&article.ArticleId, &article.Title, &article.Content, &article.MarkdownContent, &article.Status, &article.View, &article.Like, &article.DisLike, &article.CommentCount,
			&article.CollectCount, &article.UpdateTime, &article.AuthorId, &article.IsLiked, &article.IsDisLiked, &article.IsCollected)
		if err != nil {
			return nil, err
		}

		articles = append(articles, &article)
		total++
	}

	articleResult.Records = articles

	articleResult.Total = total

	return articleResult, nil
}

// 一键审核
func (r *articleRepository) ReviewAllArticle() error {
	tx, err := r.db.Begin()
	if err != nil {
		return err
	}

	defer func() {
		if err != nil {
			tx.Rollback()
		}
	}()

	_, err = tx.Exec(`
		UPDATE 
			article 
		SET 
			status = 2 
		WHERE status = 1`)
	if err != nil {
		return err
	}

	err = tx.Commit()
	return err
}
